﻿using Dapper;
using HR.Config;
using HR.IService;
using HR.Models;
using System.Data.SqlClient;

namespace HR.Service
{
    public class UsersService : IUsersService
    {
        private readonly IConfiguration Iconfiguration;
        private readonly HRDBContext db;
        private readonly string? connstr;
        public UsersService(IConfiguration iconfiguration, HRDBContext db)
        {
            Iconfiguration = iconfiguration;
            this.db = db;
            connstr = this.Iconfiguration["ConnectionStrings"];
        }

        public async Task<user?> LoginUrs(user us)
        {
            return await Task.Run(() =>
            {
                using (SqlConnection sqlc = new SqlConnection(connstr))
                {
                    string sql = $@"select * from[dbo].[users] where u_name = '{us.u_name}' and u_password = '{us.u_password}'";
                    return sqlc.Query<user>(sql).FirstOrDefault();
                }
            });
        }

        public Task<List<MenuRole>> HomeMenusFill(int rid)
        {
            //此处根据权限ID查询其下该拥有的菜单集
            return Task.Run(() =>
            {
                List<MenuRole> list1 = new List<MenuRole>();
                string ridstr = "";
                //判断是否要查询所有菜单项
                if (rid != 0)
                {
                    ridstr = $" and  [Rid] = {rid}";
                }
                using (SqlConnection sqlcon = new SqlConnection(connstr))
                {
                    //查询一级
                    string sql = $@"select distinct m.mid, mtext,murl,iconlog from [dbo].[Menu] m inner join[dbo].[RoleMenuMid] rm
                    on m.mid = rm.mid where pmid=0"+ ridstr;
                    list1 = sqlcon.Query<MenuRole>(sql).ToList();
                    //查询二级
                    foreach (MenuRole l1 in list1)
                    {
                        sql = $@"select distinct m.mid, mtext,murl,iconlog from [dbo].[Menu] m inner join[dbo].[RoleMenuMid] rm
                        on m.mid = rm.mid where pmid={l1.mid}"+ ridstr;
                        List<MenuRole> list2 = sqlcon.Query<MenuRole>(sql).ToList();
                        //查询三级
                        foreach (MenuRole l2 in list2)
                        {
                            sql = $@"select distinct m.mid, mtext,murl,iconlog from [dbo].[Menu] m inner join[dbo].[RoleMenuMid] rm
                            on m.mid = rm.mid where pmid={l2.mid}"+ ridstr;
                            l2.children = sqlcon.Query<MenuRole>(sql).ToList();
                        }
                        l1.children = list2;
                    }
                }
                return list1;
            });
        }

        public Task<Paging<UserDapper>> RoleFillUsers(int pgsize, int pgcurrent)
        {
            return Task.Run(() =>
            {
                Paging<UserDapper> pg = new Paging<UserDapper>();
                using (SqlConnection sqlcon = new SqlConnection(connstr))
                {
                    string sql = "select * from users u inner join role r on u.rid = r.rid";
                    List<UserDapper> list = sqlcon.Query<UserDapper>(sql).ToList();
                    List<UserDapper> tabs = list.Skip((pgcurrent - 1) * pgsize).Take(pgsize).ToList();
                    pg.tabs = tabs;
                    pg.rows = list.Count;
                };
                return pg;
            });
        }

        public Task<int> RoleInsertUsers(user ur)
        {
            return Task.Run(() =>
            {
                db.users.Add(ur);
                return db.SaveChanges();
            });
        }

        public Task<int> RoleUpdateUsers(user ur)
        {
            return Task.Run(() =>
            {
                user? user = db.users.Find(ur.u_id);
                user.u_name = ur.u_name;
                user.u_password = ur.u_password;
                user.u_true_name = ur.u_true_name;
                user.rid = ur.rid;
                return db.SaveChanges();
            });
        }

        public Task<int> RoleDeleteUsers(short uid)
        {
            return Task.Run(() =>
            {
                user? user = db.users.Find(uid);
                db.users.Remove(user);
                return db.SaveChanges();
            });
        }
    }
}
